import datetime
from db_conn import pd_conn_zfw, executeSql


def queryPayTypes():
    sql = 'SELECT DISTINCT f_pay_type FROM t_order_income_config WHERE f_status = 1 AND f_is_refund = 0'
    field, result = executeSql(sql)

    payTypes = ''
    for p in result:

        if not payTypes:
            payTypes += p[0]
        else:
            payTypes += ',' + p[0]

    return payTypes


def query_country(st, et):
    year = datetime.datetime.strptime(st, '%Y-%m-%d').year
    table_name = 't_order' if year == 2021 or year == 2022 else 't_order_' + str(year)
    payTypes = queryPayTypes()
    sql1 = f'''
    SELECT
        if(f_location_type = 1,'社区','油站') as `站点类型`,
        COUNT(DISTINCT(f_user_id)) as `分母`,
        COUNT(f_user_id) as `分子`,
        COUNT(f_user_id) /  COUNT(DISTINCT(f_user_id)) as `消费频次`
    FROM
        {table_name} a force index(order_date_index)
        INNER JOIN t_factory f ON f.f_id = a.f_factory_id
    WHERE
        f_order_date >= '{st}'
        AND f_order_date < '{et}'
        AND (
            a.f_status IN (50, 60)
            OR (
                a.f_status = 15
                AND a.f_pay_type IN ({payTypes})
            )
        )
        AND f_is_insider = 0
        AND f_service_id = 2002
    GROUP BY
        f_location_type
    '''
    sql2 = f'''
    SELECT
        if(f_location_type = 1,'社区','油站') as `站点类型`,
        COUNT(DISTINCT(f_user_id)) as `分母`,
        COUNT(f_user_id) as `分子`,
        COUNT(f_user_id) /  COUNT(DISTINCT(f_user_id)) as `消费频次`
    FROM
        {table_name} a force index(order_date_index)
        INNER JOIN t_factory f ON f.f_id = a.f_factory_id
    WHERE
        f_order_date >= '{st}'
        AND f_order_date < '{et}'
        AND (
            a.f_status IN (50, 60)
            OR (
                a.f_status = 15
                AND a.f_pay_type IN ({payTypes})
            )
        )
        AND f_is_insider = 0
        AND f_service_id = 2002
        and f.f_city_id = 3301
    GROUP BY
        f_location_type
    '''
    return pd_conn_zfw(sql1), pd_conn_zfw(sql2)


if __name__ == '__main__':

    startTime = '2022-07-01'
    endTime = '2022-10-01'

    print('预计执行4分钟...')
    t1 = datetime.datetime.now()
    result1, result2 = query_country(startTime, endTime)
    t2 = datetime.datetime.now()

    print('----------- （全国） -----------')
    print(result1)
    print('----------- （杭州） -----------')
    print(result2)
    print(t2 - t1)

